-- Creator:       MySQL Workbench 5.2.25/ExportSQLite plugin 2009.12.02
-- Author:        Unknown
-- Caption:       New Model
-- Project:       Name of the project
-- Changed:       2011-03-16 11:35
-- Created:       2011-03-11 12:40
PRAGMA foreign_keys = OFF;

-- Schema: mydb
BEGIN;
CREATE TABLE "Node"(
  "node_id" INTEGER NOT NULL,
  "theme_id" INTEGER NOT NULL,
  "display_name" VARCHAR(45),
  "comment" VARCHAR(255),
  "poi" BOOLEAN,
  "way" BOOLEAN,
  "area" BOOLEAN,
  "icon_uri" VARCHAR(255),
  "wiki_uri" VARCHAR(45),
  "use_stat" INTEGER,
  "is_bundle" BOOLEAN,
  PRIMARY KEY("node_id","theme_id")
);
CREATE TABLE "Key"(
  "osm_name" VARCHAR(45) PRIMARY KEY NOT NULL,
  "use_stat" INTEGER,
  "wiki_uri" VARCHAR(255),
  "type" TEXT CHECK("type" IN('enum','number','text'))
);
CREATE TABLE "Value"(
  "osm_name" VARCHAR(45) PRIMARY KEY NOT NULL,
  "use_stat" INTEGER
);
CREATE TABLE "Feature"(
  "key_name" VARCHAR(45) NOT NULL,
  "value_name" VARCHAR(45) NOT NULL,
  "comment" VARCHAR(255),
  "poi" BOOLEAN,
  "way" BOOLEAN,
  "area" BOOLEAN,
  "icon_uri" VARCHAR(255),
  "wiki_uri" VARCHAR(255),
  "render_uri" VARCHAR(255),
  "photo_uri" VARCHAR(255),
  "use_stat" INTEGER,
  PRIMARY KEY("key_name","value_name"),
  CONSTRAINT "fk_Tag_Key1"
    FOREIGN KEY("key_name")
    REFERENCES "Key"("osm_name"),
  CONSTRAINT "fk_Tag_Value1"
    FOREIGN KEY("value_name")
    REFERENCES "Value"("osm_name")
);
CREATE INDEX "Feature.fk_Feature_Key" ON "Feature"("key_name");
CREATE INDEX "Feature.fk_Feature_Value" ON "Feature"("value_name");
CREATE TABLE "bundles_to_features"(
  "bundle_id" INTEGER NOT NULL,
  "key_name" VARCHAR(45) NOT NULL,
  "value_name" VARCHAR(45) NOT NULL,
  PRIMARY KEY("value_name","key_name","bundle_id"),
  CONSTRAINT "fk_bundles_to_tags_Node1"
    FOREIGN KEY("bundle_id")
    REFERENCES "Node"("node_id"),
  CONSTRAINT "fk_bundles_to_tags_Tag1"
    FOREIGN KEY("key_name","value_name")
    REFERENCES "Feature"("key_name","value_name")
);
CREATE INDEX "bundles_to_features.fk_bundles_to_features_Node" ON "bundles_to_features"("bundle_id");
CREATE INDEX "bundles_to_features.fk_bundles_to_features_Feature" ON "bundles_to_features"("key_name","value_name");
COMMIT;
